Many applications use a database to store their data. The database for an application is typically designed by the developer of the application to include a table for each entity used by the application. Each entity table contains a row for each specific entity and various columns for storing properties of the entity. For example, in the case of a project management application, the entities may include a project, a task, an assignment, or a resource, and a specific entity is a specific project, a specific task, a specific assignment, or a specific resource. The project table may contain a project identifier column, a project name column, a project start date column, and so on. The project identifier column contains the unique identifier of a specific project and is referred to as a “unique key” of the project table. Each row of the project table corresponds to a specific project, and the cells of a row contain the data of that specific project for the columns. A task table may contain a task identifier column, project identifier column, task name column, and so on. The task identifier column contains the unique identifiers of specific tasks. The project identifier column contains the project identifier of the specific project with which the task is associated and is referred to as a “foreign key.” Each row of the task table corresponds to a specific task.
Complex applications may have many hundreds of properties associated with an entity. This presents problems for databases that limit the number of columns of a table. For example, some databases may limit the number of columns to 128 or 256. To overcome this problem, applications may store data for an entity in multiple database tables. For example, if an application needs 300 columns to represent the properties of an entity and the limit on the number of columns of a table is 128, then the developer of the application may divide the 300 columns across three tables with 101 columns in each table. Each table may contain a unique key column and 100 property columns. When the properties of a specific entity is added to the database, the application generates a unique identifier for that specific entity and adds a row to each of the three tables with its unique key set to that unique identifier. The combination of the rows from the three tables with the same unique identifier corresponds to the columns for the entity. To access the data for that specific entity, the application may join the three tables. As a result, at least for viewing purposes, the join results in a logical data view that contains the unique identifier column and the 300 property columns.
Even though these complex applications have many properties associated with an entity, referred to as “standard” properties or columns, users may need to have additional properties associated with an entity. For example, in the case of a project management application, a user may need to track project type and project status, which may have no corresponding standard column. To assist users in defining their own properties for an entity, applications may allow custom columns to be defined. For example, a user may define a type custom column and a status custom column to track the type and status of projects. The custom column can be considered just one more column associated with an entity.
Although custom columns could be supported by modifying the schema of the database, such modifications can be time-consuming and error-prone, especially if performed by the users of the application. To allow users the flexibility to create custom columns without modifying the schema of the database, some applications use a “pivot” table to store information relating to custom columns. A pivot table for an entity would typically include a key column, a custom column name column, and a data column. Whenever data for a custom column is to be added for a specific entity, a new row is added to the pivot table that contains the unique key associated with that specific entity, the name of the custom column, and the data.
The use of pivot tables to represent custom columns may make it difficult for a user to retrieve all the properties associated with a specific entity. In particular, although a join can be used to combine the data of standard tables, the data of the custom columns cannot be joined so easily. Moreover, even if with only standard tables are joined to provide a logical data view, some databases may not allow updates via the logical data view. It would be desirable to provide a logical data view that would integrate both standard columns and custom columns and would allow for the updating of data of both standard columns and custom columns via a logical data view.